#!/usr/bin/env python3
"""
亚马逊Selling Partner API订单下载工具
提供从Amazon SP-API获取订单信息的功能
"""
DATABASE_URL = "mysql+pymysql://root:Zb_200407@gz-cdb-d4j7h16x.sql.tencentcdb.com:22333/walmart"
from random import randint,random
from decimal import Decimal
import json
import traceback
import requests
import json
import time
import logging
import os
from datetime import datetime, timedelta, timezone
from typing import Dict, List, Optional, Any, Union
from databasemodel import AmazonOrder, AmazonOrderItem
# 设置日志
   
from sqlalchemy import create_engine, func
from sqlalchemy.orm import sessionmaker
from app.intereface_amazon.databasemodel import AmazonOrder, AmazonOrderItem
 
import logging
import time
import traceback
from decimal import Decimal
    
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler("amazon_orders.log"),
        logging.StreamHandler()
    ]
)
logger = logging.getLogger("amazon_orders")
def import_amazon_orders(order_data_list):
    """
    导入Amazon订单和订单明细到数据库
    
    Args:
        order_data_list: 包含订单数据的列表
        
    Returns:
        tuple: (导入的订单数量, 导入的订单项目数量)
    """
    engine = create_engine(DATABASE_URL)
    Session = sessionmaker(bind=engine)
    session = Session()
    
    # 设置日志
    logging.basicConfig(
        level=logging.INFO,
        format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
    )
    logger = logging.getLogger('amazon_import')
    
    # 统计计数器
    order_inserted = 0
    order_updated = 0
    item_inserted = 0
    item_updated = 0
    
    try:
        for order_data in order_data_list:
            try:
                # 检查是否是字符串，需要转换为字典
                if isinstance(order_data, str):
                    try:
                        import ast
                        order_data = ast.literal_eval(order_data)
                    except (SyntaxError, ValueError) as e:
                        logger.error(f"无法解析订单数据: {e}")
                        continue
                
                # 提取订单ID
                amazon_order_id = order_data.get('AmazonOrderId')
                if not amazon_order_id:
                    logger.warning("订单数据缺少AmazonOrderId，跳过")
                    continue
                
                # 检查订单是否已存在
                from databasemodel import AmazonOrder, AmazonOrderItem
                existing_order = session.query(AmazonOrder).filter_by(
                    amazon_order_id=amazon_order_id
                ).first()
                
                # 提取订单价格
                order_total_amount = None
                currency_code = None
                if 'OrderTotal' in order_data:
                    order_total = order_data['OrderTotal']
                    if 'Amount' in order_total:
                        order_total_amount = Decimal(str(order_total['Amount']))
                    currency_code = order_total.get('CurrencyCode')
                
                # 提取买家信息
                buyer_email = None
                if 'BuyerInfo' in order_data and 'BuyerEmail' in order_data['BuyerInfo']:
                    buyer_email = order_data['BuyerInfo']['BuyerEmail']
                
                # 提取配送地址
                shipping_address_state = None
                shipping_address_postal_code = None
                shipping_address_city = None
                shipping_address_country_code = None
                
                if 'ShippingAddress' in order_data:
                    shipping_address = order_data['ShippingAddress']
                    shipping_address_state = shipping_address.get('StateOrRegion')
                    shipping_address_postal_code = shipping_address.get('PostalCode')
                    shipping_address_city = shipping_address.get('City')
                    shipping_address_country_code = shipping_address.get('CountryCode')
                
                # 解析日期
                def parse_date(date_str):
                    if not date_str:
                        return None
                    try:
                        if date_str.endswith('Z'):
                            date_str = date_str[:-1] + '+00:00'
                        return datetime.fromisoformat(date_str)
                    except ValueError:
                        try:
                            return datetime.strptime(date_str, '%Y-%m-%dT%H:%M:%S')
                        except ValueError:
                            return None
                
                # 准备订单数据
                order_data_dict = {
                    'amazon_order_id': amazon_order_id,
                    'seller_order_id': order_data.get('SellerOrderId'),
                    'purchase_date': parse_date(order_data.get('PurchaseDate')),
                    'last_update_date': parse_date(order_data.get('LastUpdateDate')),
                    'order_status': order_data.get('OrderStatus'),
                    'fulfillment_channel': order_data.get('FulfillmentChannel'),
                    'sales_channel': order_data.get('SalesChannel'),
                    'ship_service_level': order_data.get('ShipServiceLevel'),
                    'shipment_service_level_category': order_data.get('ShipmentServiceLevelCategory'),
                    'number_of_items_shipped': int(order_data.get('NumberOfItemsShipped', 0)),
                    'number_of_items_unshipped': int(order_data.get('NumberOfItemsUnshipped', 0)),
                    'payment_method': order_data.get('PaymentMethod'),
                    'payment_method_details': json.dumps(order_data.get('PaymentMethodDetails', [])),
                    'marketplace_id': order_data.get('MarketplaceId'),
                    'order_type': order_data.get('OrderType'),
                    'earliest_ship_date': parse_date(order_data.get('EarliestShipDate')),
                    'latest_ship_date': parse_date(order_data.get('LatestShipDate')),
                    'is_business_order': bool(order_data.get('IsBusinessOrder')),
                    'is_prime': bool(order_data.get('IsPrime')),
                    'is_premium_order': bool(order_data.get('IsPremiumOrder')),
                    'is_global_express_enabled': bool(order_data.get('IsGlobalExpressEnabled')),
                    'is_replacement_order': order_data.get('IsReplacementOrder') == 'true',
                    'is_sold_by_ab': bool(order_data.get('IsSoldByAB')),
                    'is_ispu': bool(order_data.get('IsISPU')),
                    'is_access_point_order': bool(order_data.get('IsAccessPointOrder')),
                    'has_regulated_items': bool(order_data.get('HasRegulatedItems')),
                    'buyer_email': buyer_email,
                    'currency_code': currency_code,
                    'order_total_amount': order_total_amount,
                    'shipping_address_state': shipping_address_state,
                    'shipping_address_postal_code': shipping_address_postal_code,
                    'shipping_address_city': shipping_address_city,
                    'shipping_address_country_code': shipping_address_country_code,
                }
                
                # 创建或更新订单
                if existing_order:
                    # 更新现有订单
                    for key, value in order_data_dict.items():
                        if hasattr(existing_order, key) and value is not None:
                            setattr(existing_order, key, value)
                    amazon_order = existing_order
                    order_updated += 1
                    logger.info(f"更新订单: {amazon_order_id}")
                else:
                    # 创建新订单
                    amazon_order = AmazonOrder(**order_data_dict)
                    session.add(amazon_order)
                    order_inserted += 1
                    logger.info(f"新增订单: {amazon_order_id}")
                
                # 处理订单明细
                if 'order_items' in order_data and order_data['order_items']:
                    order_items = order_data['order_items']
                    for item in order_items:
                        # 提取订单项ID
                        order_item_id = item.get('OrderItemId')
                        if not order_item_id:
                            logger.warning(f"订单项缺少OrderItemId，跳过 (订单: {amazon_order_id})")
                            continue
                        
                        # 检查订单项是否已存在
                        existing_item = session.query(AmazonOrderItem).filter_by(
                            order_item_id=order_item_id
                        ).first()
                        
                        # 提取价格信息
                        item_price = None
                        item_price_currency = None
                        if 'ItemPrice' in item:
                            if 'Amount' in item['ItemPrice']:
                                item_price = Decimal(str(item['ItemPrice']['Amount']))
                            item_price_currency = item['ItemPrice'].get('CurrencyCode')
                        
                        # 提取税费信息
                        item_tax = None
                        if 'ItemTax' in item and 'Amount' in item['ItemTax']:
                            item_tax = Decimal(str(item['ItemTax']['Amount']))
                        
                        # 提取运费信息
                        shipping_price = None
                        if 'ShippingPrice' in item and 'Amount' in item['ShippingPrice']:
                            shipping_price = Decimal(str(item['ShippingPrice']['Amount']))
                        
                        shipping_tax = None
                        if 'ShippingTax' in item and 'Amount' in item['ShippingTax']:
                            shipping_tax = Decimal(str(item['ShippingTax']['Amount']))
                        
                        # 提取促销折扣
                        promotion_discount = None
                        if 'PromotionDiscount' in item and 'Amount' in item['PromotionDiscount']:
                            promotion_discount = Decimal(str(item['PromotionDiscount']['Amount']))
                        
                        # 提取礼品包装费
                        gift_wrap_price = None
                        if 'GiftWrapPrice' in item and 'Amount' in item['GiftWrapPrice']:
                            gift_wrap_price = Decimal(str(item['GiftWrapPrice']['Amount']))
                        
                        # 准备订单项数据
                        item_data_dict = {
                            'order_id': amazon_order.id if hasattr(amazon_order, 'id') else None,
                            'order_item_id': order_item_id,
                            'asin': item.get('ASIN', ''),
                            'seller_sku': item.get('SellerSKU'),
                            'title': item.get('Title'),
                            'quantity_ordered': int(item.get('QuantityOrdered', 0)),
                            'quantity_shipped': int(item.get('QuantityShipped', 0)),
                            'item_price': item_price,
                            'item_price_currency': item_price_currency,
                            'item_tax': item_tax,
                            'shipping_price': shipping_price,
                            'shipping_tax': shipping_tax,
                            'promotion_discount': promotion_discount,
                            'condition': item.get('ConditionId'),
                            'condition_note': item.get('ConditionNote'),
                            'is_gift': bool(item.get('IsGift')),
                            'gift_message': item.get('GiftMessageText'),
                            'gift_wrap_price': gift_wrap_price,
                        }
                        
                        # 创建或更新订单项
                        if existing_item:
                            # 只有当订单ID存在时才更新
                            if 'order_id' in item_data_dict and item_data_dict['order_id'] is not None:
                                # 更新现有订单项
                                for key, value in item_data_dict.items():
                                    if hasattr(existing_item, key) and value is not None:
                                        setattr(existing_item, key, value)
                                item_updated += 1
                                logger.info(f"更新订单项: {order_item_id}")
                        else:
                            # 只有当订单ID存在时才创建新订单项
                            if 'order_id' in item_data_dict and item_data_dict['order_id'] is not None:
                                # 创建新订单项
                                order_item = AmazonOrderItem(**item_data_dict)
                                session.add(order_item)
                                item_inserted += 1
                                logger.info(f"新增订单项: {order_item_id}")
                
                # 定期提交，避免事务过大
                if (order_inserted + order_updated) % 10 == 0:
                    session.commit()
            
            except Exception as e:
                logger.error(f"处理订单时出错: {str(e)}")
                traceback.print_exc()
                # 继续处理下一个订单，不中断整个过程
        
        # 提交所有剩余更改
        session.commit()
        logger.info(f"导入完成! 新增订单: {order_inserted}, 更新订单: {order_updated}, 新增订单项: {item_inserted}, 更新订单项: {item_updated}")
        return (order_inserted + order_updated, item_inserted + item_updated)
    
    except Exception as e:
        # 发生错误时回滚
        logger.error(f"导入过程中出错: {str(e)}")
        traceback.print_exc()
        session.rollback()
        return (0, 0)
    
    finally:
        # 总是关闭会话
        session.close()
class AmazonOrdersClient:
    """亚马逊订单API客户端"""
    
    def __init__(self, credentials: Dict[str, str], marketplace: str = "ATVPDKIKX0DER"):
        """
        初始化亚马逊订单API客户端
        
        Args:
            credentials: 包含API凭证的字典
            marketplace: 市场ID，默认为美国 (ATVPDKIKX0DER)
        """
        self.credentials = credentials
        self.marketplace = marketplace
        self.base_url = "https://sellingpartnerapi-na.amazon.com"  # 北美地区，可根据区域调整
        self.access_token = credentials.get("access_token")
        self.refresh_token = credentials.get("refresh_token")
        self.client_id = credentials.get("client_id")
        self.client_secret = credentials.get("client_secret")
        self.token_type = credentials.get("token_type", "bearer")
        self.expires_at = None
        
        # 如果提供了expires_at字符串，转换为datetime
        if "expires_at" in credentials and credentials["expires_at"]:
            try:
                self.expires_at = datetime.strptime(credentials["expires_at"], "%Y-%m-%d %H:%M:%S")
            except ValueError:
                logger.warning("提供的expires_at格式无效，将在首次API调用时刷新令牌")
    def import_orders_with_items(self, orders_data):
        """
        导入订单数据并将订单明细存储到子表
        
        Args:
            orders_data: 从API获取的订单数据
        
        Returns:
            新导入的订单数量
        """

        
        new_orders_count = 0
        items_count = 0
        
        try:
            orders = orders_data.get('payload', {}).get('Orders', [])
            for order in orders:
                amazon_order_id = order.get('AmazonOrderId')
                if not amazon_order_id:
                    continue
                    
                # 检查订单是否已存在
                order_exists = AmazonOrder.objects.filter(amazon_order_id=amazon_order_id).exists()
                
                if not order_exists:
                    # 提取订单数据并创建新订单
                    order_data = self.extract_order_data(order)
                    amazon_order = AmazonOrder.objects.create(**order_data)
                    new_orders_count += 1
                    print(f"创建新订单: {amazon_order_id}")
                    order_items = self.get_all_order_items(amazon_order_id)
                    if order_items:
                        for item in order_items:
                            order_item_id = item.get('OrderItemId')
                            item_data = self.extract_item_data(item, amazon_order)
                            AmazonOrderItem.objects.create(**item_data)
                            items_count += 1
                            print(f"创建订单项: {order_item_id} (订单: {amazon_order_id})")
                else:
                    # 获取现有订单
                    amazon_order = AmazonOrder.objects.get(amazon_order_id=amazon_order_id)
                    print(f"订单已存在: {amazon_order_id}")
                 
            
            print(f"导入完成，新增订单: {new_orders_count}，新增订单项: {items_count}")
            return new_orders_count
        
        except Exception as e:
            import traceback
            print(f"导入订单时出错: {str(e)}")
            traceback.print_exc()
            return 0

    def extract_order_data(self, order):
        """
        从API响应中提取订单数据
        
        Args:
            order: 单个订单的API响应数据
            
        Returns:
            包含订单字段的字典
        """
        # 提取订单价格
        order_total_amount = None
        currency_code = None
        if 'OrderTotal' in order:
            order_total = order['OrderTotal']
            order_total_amount = float(order_total.get('Amount', 0))
            currency_code = order_total.get('CurrencyCode')
        
        # 提取买家信息
        buyer_email = None
        if 'BuyerInfo' in order and 'BuyerEmail' in order['BuyerInfo']:
            buyer_email = order['BuyerInfo']['BuyerEmail']
        
        # 提取配送地址
        shipping_address_state = None
        shipping_address_postal_code = None
        shipping_address_city = None
        shipping_address_country_code = None
        
        if 'ShippingAddress' in order:
            shipping_address = order['ShippingAddress']
            shipping_address_state = shipping_address.get('StateOrRegion')
            shipping_address_postal_code = shipping_address.get('PostalCode')
            shipping_address_city = shipping_address.get('City')
            shipping_address_country_code = shipping_address.get('CountryCode')
        
        # 构建订单数据字典
        return {
            'amazon_order_id': order.get('AmazonOrderId'),
            'seller_order_id': order.get('SellerOrderId'),
            'purchase_date': self.parse_date(order.get('PurchaseDate')),
            'last_update_date': self.parse_date(order.get('LastUpdateDate')),
            'order_status': order.get('OrderStatus'),
            'fulfillment_channel': order.get('FulfillmentChannel'),
            'sales_channel': order.get('SalesChannel'),
            'ship_service_level': order.get('ShipServiceLevel'),
            'shipment_service_level_category': order.get('ShipmentServiceLevelCategory'),
            'number_of_items_shipped': int(order.get('NumberOfItemsShipped', 0)),
            'number_of_items_unshipped': int(order.get('NumberOfItemsUnshipped', 0)),
            'payment_method': order.get('PaymentMethod'),
            'payment_method_details': json.dumps(order.get('PaymentMethodDetails', [])),
            'marketplace_id': order.get('MarketplaceId'),
            'order_type': order.get('OrderType'),
            'earliest_ship_date': self.parse_date(order.get('EarliestShipDate')),
            'latest_ship_date': self.parse_date(order.get('LatestShipDate')),
            'is_business_order': bool(order.get('IsBusinessOrder')),
            'is_prime': bool(order.get('IsPrime')),
            'is_premium_order': bool(order.get('IsPremiumOrder')),
            'is_global_express_enabled': bool(order.get('IsGlobalExpressEnabled')),
            'is_replacement_order': order.get('IsReplacementOrder') == 'true',
            'is_sold_by_ab': bool(order.get('IsSoldByAB')),
            'is_ispu': bool(order.get('IsISPU')),
            'is_access_point_order': bool(order.get('IsAccessPointOrder')),
            'has_regulated_items': bool(order.get('HasRegulatedItems')),
            'buyer_email': buyer_email,
            'currency_code': currency_code,
            'order_total_amount': order_total_amount,
            'shipping_address_state': shipping_address_state,
            'shipping_address_postal_code': shipping_address_postal_code,
            'shipping_address_city': shipping_address_city,
            'shipping_address_country_code': shipping_address_country_code
        }

    def extract_item_data(self, item, order):
        """
        从API响应中提取订单项目数据
        
        Args:
            item: 单个订单项目的API响应数据
            order: 关联的订单对象
            
        Returns:
            包含订单项目字段的字典
        """
        from decimal import Decimal
        
        # 提取价格信息
        item_price = Decimal('0.00')
        item_price_currency = None
        if 'ItemPrice' in item and 'Amount' in item['ItemPrice']:
            item_price = Decimal(str(item['ItemPrice']['Amount']))
            item_price_currency = item['ItemPrice'].get('CurrencyCode')
        
        # 提取税费信息
        item_tax = Decimal('0.00')
        if 'ItemTax' in item and 'Amount' in item['ItemTax']:
            item_tax = Decimal(str(item['ItemTax']['Amount']))
        
        # 提取运费信息
        shipping_price = Decimal('0.00')
        if 'ShippingPrice' in item and 'Amount' in item['ShippingPrice']:
            shipping_price = Decimal(str(item['ShippingPrice']['Amount']))
        
        shipping_tax = Decimal('0.00')
        if 'ShippingTax' in item and 'Amount' in item['ShippingTax']:
            shipping_tax = Decimal(str(item['ShippingTax']['Amount']))
        
        # 提取促销折扣
        promotion_discount = Decimal('0.00')
        if 'PromotionDiscount' in item and 'Amount' in item['PromotionDiscount']:
            promotion_discount = Decimal(str(item['PromotionDiscount']['Amount']))
        
        # 提取礼品信息
        gift_wrap_price = Decimal('0.00')
        if 'GiftWrapPrice' in item and 'Amount' in item['GiftWrapPrice']:
            gift_wrap_price = Decimal(str(item['GiftWrapPrice']['Amount']))
        
        # 构建订单项目数据字典
        return {
            'order': order,  # 关联到订单对象
            'order_item_id': item.get('OrderItemId'),
            'asin': item.get('ASIN', ''),
            'seller_sku': item.get('SellerSKU', ''),
            'title': item.get('Title', ''),
            'quantity_ordered': int(item.get('QuantityOrdered', 0)),
            'quantity_shipped': int(item.get('QuantityShipped', 0)),
            'item_price': item_price,
            'item_price_currency': item_price_currency,
            'item_tax': item_tax,
            'shipping_price': shipping_price,
            'shipping_tax': shipping_tax,
            'promotion_discount': promotion_discount,
            'condition': item.get('ConditionId', ''),
            'condition_note': item.get('ConditionNote', ''),
            'is_gift': item.get('IsGift', False),
            'gift_message': item.get('GiftMessageText', ''),
            'gift_wrap_price': gift_wrap_price
        }

    def parse_date(self, date_str):
        """
        解析ISO格式的日期字符串
        
        Args:
            date_str: ISO格式的日期字符串
            
        Returns:
            datetime对象或None
        """
        if not date_str:
            return None
        
        from datetime import datetime
        try:
            # 处理带Z后缀的ISO日期格式
            if date_str.endswith('Z'):
                date_str = date_str[:-1] + '+00:00'
            return datetime.fromisoformat(date_str)
        except ValueError:
            try:
                # 尝试其他日期格式
                return datetime.strptime(date_str, '%Y-%m-%dT%H:%M:%S')
            except ValueError:
                return None
    def refresh_access_token(self) -> bool:
        """
        刷新访问令牌
        
        Returns:
            bool: 刷新是否成功
        """
        # 检查现有令牌是否仍然有效
        if self.access_token and self.expires_at and datetime.now() < self.expires_at:
            logger.debug("现有访问令牌仍然有效，不需要刷新")
            return True
            
        logger.info("刷新访问令牌...")
        
        # 在实际应用中，这里应该调用Amazon LWA服务来刷新令牌
        # 为简化示例，这里假设已经提供了有效的访问令牌
        if not self.access_token:
            logger.error("未提供访问令牌，且无法自动刷新")
            return False
            
        # 如果提供了expires_in，设置新的过期时间
        expires_in = self.credentials.get("expires_in", 3600)  # 默认1小时
        self.expires_at = datetime.now() + timedelta(seconds=expires_in)
            
        logger.info("访问令牌可用")
        return True
    import requests
    from datetime import datetime, timedelta
    from dateutil.parser import isoparse

    # 替换为你的卖家 ID
    SELLER_ID = "A26FR646PJC2K3"
    


    def get_amazon_orders(self,access_token, seller_id="A26FR646PJC2K3", region="na"):
        # 构建请求头
        headers = {
            "Authorization": f"Bearer {access_token}",
            "x-amz-access-token": access_token,
            "x-amz-seller-id": seller_id,
            "Content-Type": "application/json",
        }
        
        # 1. 计算符合Amazon要求的时间范围
        current_time = datetime.now(timezone.utc)
        created_before = (current_time - timedelta(minutes=3)).isoformat()  # 比当前时间早3分钟
        created_after = (current_time - timedelta(days=29, minutes=3)).isoformat()  # 29天前的时间点

        # 2. 确保ISO时间格式正确（移除毫秒部分以确保兼容性）
        created_before = created_before.split('.')[0] + 'Z'
        created_after = created_after.split('.')[0] + 'Z'

        # 3. 打印时间范围以便验证
        print(f"查询时间范围: {created_after} 到 {created_before}")
        print(f"当前时间: {current_time.isoformat()}")

        # 4. 设置正确的请求参数
        params = {
            "CreatedAfter": created_after,
            "CreatedBefore": created_before,  # 比当前时间早至少3分钟
            "OrderStatuses": "Shipped,Unshipped,PartiallyShipped,Pending",
            "MaxResultsPerPage": "100",
            "MarketplaceIds": ["ATVPDKIKX0DER"]  # 作为数组传递，确保使用正确的大小写
        }
        
        # 构建API端点（根据区域选择URL）
        endpoint = f"https://sellingpartnerapi-{region}.amazon.com/orders/v0/orders"
        
        try:
            # 打印完整请求信息
            print(f"请求URL: {endpoint}")
            print(f"请求头: {json.dumps({k: (v if k != 'Authorization' else 'Bearer ***') for k, v in headers.items()})}")
            print(f"请求参数: {json.dumps(params)}")
            
            response = requests.get(endpoint, headers=headers, params=params)
            print(f"响应状态码: {response.status_code}")
            print(f"响应内容: {response.text}")
            
            response.raise_for_status()
            orders_data = response.json()
            print(f"获取的订单数: {len(orders_data.get('payload', {}).get('Orders', []))}")
            #在这里循环读取订单，如果订单在表里不存在，则读取订单的明细信息
            self.import_orders_with_items(orders_data)
            return orders_data
        except Exception as e:
            traceback.print_exc()
            print(f"请求失败: {str(e)}")
            if hasattr(e, 'response') and e.response:
                print(f"错误状态码: {e.response.status_code}")
                print(f"错误响应: {e.response.text}")
            return None

    def _request(self, method: str, endpoint: str, params: Dict = None, data: Dict = None) -> Dict:
        """
        发送API请求
        
        Args:
            method: HTTP方法
            endpoint: API端点
            params: 查询参数
            data: 请求体数据
            
        Returns:
            Dict: API响应
        """
        # 确保有有效令牌
        if not self.refresh_access_token():
            raise Exception("无法获取有效访问令牌")
            
        url = f"{self.base_url}{endpoint}"
        headers = {
            "Authorization": f"{self.token_type} {self.access_token}",
            "Content-Type": "application/json",
            "Accept": "application/json",
            "x-amz-access-token": self.access_token  # 有些端点需要这个头部
        }
        
        try:
            logger.debug(f"发送 {method} 请求到 {url}")
            
            if method.lower() == "get":
                response = requests.get(url, headers=headers, params=params)
            elif method.lower() == "post":
                response = requests.post(url, headers=headers, params=params, json=data)
            else:
                raise ValueError(f"不支持的HTTP方法: {method}")
                
            response.raise_for_status()
            return response.json()
            
        except requests.exceptions.RequestException as e:
            logger.error(f"API请求失败: {str(e)}")
            if hasattr(e, "response") and e.response:
                logger.error(f"状态码: {e.response.status_code}")
                logger.error(f"响应: {e.response.text}")
            raise
    
    def get_orders(self, created_after: Optional[str] = None, 
                   created_before: Optional[str] = None, 
                   order_statuses: List[str] = None, 
                   max_results: int = 100,
                   next_token: Optional[str] = None) -> Dict:
        """
        获取订单列表
        
        Args:
            created_after: 开始时间 (ISO 8601格式)
            created_before: 结束时间 (ISO 8601格式)
            order_statuses: 订单状态列表
            max_results: 每页最大结果数
            next_token: 用于分页的令牌
            
        Returns:
            Dict: 包含订单列表的响应
        """
        endpoint = "/orders/v0/orders"
        params = {}
        
        if next_token:
            params["NextToken"] = next_token
        else:
            # 设置默认订单状态
            if not order_statuses:
                order_statuses = ["Shipped", "Unshipped", "PartiallyShipped", "Pending"]
                
            # 使用实际的当前日期，而不是2025年
            current_date = datetime.now()
            # 查询过去30天的订单
            created_after = (current_date - timedelta(days=30)).isoformat()
            created_before = current_date.isoformat()
            
            params.update({
                "CreatedAfter": created_after,
                "CreatedBefore": created_before,
                "OrderStatuses": ",".join(order_statuses),
                "MaxResultsPerPage": str(max_results),
                "MarketplaceIds": self.marketplace
            })
        
        logger.info(f"获取订单列表: {params}")
        return self._request("GET", endpoint, params=params)
    
    def get_order(self, client, order_id: str) -> Dict:
        """
        获取特定订单的详情
        
        Args:
            order_id: 亚马逊订单ID
            
        Returns:
            Dict: 订单详情
        """
        endpoint = f"/orders/v0/orders/{order_id}"
        logger.info(f"获取订单详情: {order_id}")
        return self._request("GET", endpoint)
    
    def get_order_items(self, order_id: str, next_token: Optional[str] = None) -> Dict:
        """
        获取订单中的商品
        
        Args:
            order_id: 亚马逊订单ID
            next_token: 用于分页的令牌
            
        Returns:
            Dict: 包含订单商品的响应
        """
        endpoint = f"/orders/v0/orders/{order_id}/orderItems"
        params = {}
        
        if next_token:
            params["NextToken"] = next_token
            
        logger.info(f"获取订单商品: {order_id}")
        return self._request("GET", endpoint, params=params)
    
    def get_all_order_items(self, order_id: str) -> List[Dict]:
        """
        获取订单的所有商品（处理分页）
        
        Args:
            order_id: 亚马逊订单ID
            
        Returns:
            List[Dict]: 订单商品列表
        """
        items = []
        next_token = None
        
        while True:
            response = self.get_order_items(order_id, next_token)
            items.extend(response.get("payload", {}).get("OrderItems", []))
            
            next_token = response.get("payload", {}).get("NextToken")
            if not next_token:
                break
                
            # 避免API限制
            time.sleep(0.5)
            
        return items
    
    def get_all_orders(self, created_after: Optional[str] = None, 
                        created_before: Optional[str] = None, 
                        order_statuses: List[str] = None, 
                        max_results: int = 100) -> List[Dict]:
        """
        获取所有订单（处理分页）
        
        Args:
            created_after: 开始时间 (ISO 8601格式)
            created_before: 结束时间 (ISO 8601格式)
            order_statuses: 订单状态列表
            max_results: 每页最大结果数
            
        Returns:
            List[Dict]: 订单列表
        """
        orders = []
        next_token = None
        
        while True:
            if next_token:
                response = self.get_orders(next_token=next_token)
            else:
                # response = self.get_orders(
                #     created_after=created_after,
                #     created_before=created_before,
                #     order_statuses=order_statuses,
                #     max_results=max_results
                # )
                response = self.get_orders(
                    created_after=created_after
                    
                )
                
            orders.extend(response.get("payload", {}).get("Orders", []))
            
            next_token = response.get("payload", {}).get("NextToken")
            if not next_token:
                break
                
            # 避免API限制
            time.sleep(1)
            
        return orders
    
    def get_order_with_items(self,client, order_id: str) -> Dict:
        """
        获取订单详情和商品信息
        
        Args:
            order_id: 亚马逊订单ID
            
        Returns:
            Dict: 包含订单详情和商品的字典
        """
        # 获取订单详情
        order_response = self.get_order(client,order_id)
        order = order_response.get("payload", {})
        
        # 获取订单商品
        items = self.get_all_order_items(order_id)
        
        # 将商品添加到订单
        order["Items"] = items
        
        return order
     


def get_specific_order(client: AmazonOrdersClient, order_id: str) -> None:
    """
    获取并显示特定订单的详细信息
    
    Args:
        client: AmazonOrdersClient实例
        order_id: 要获取的订单ID
    """
    try:
        # 获取订单和商品
        order = client.get_order_with_items(client,order_id)

        
        # 打印订单信息
        print("\n订单详情:")
        print(f"订单ID: {order.get('AmazonOrderId')}")
        print(f"购买日期: {order.get('PurchaseDate')}")
        print(f"订单状态: {order.get('OrderStatus')}")
        print(f"发货方式: {order.get('FulfillmentChannel')}")
        
        # 打印商品信息
        items = order.get("Items", [])
        print(f"\n订单商品 ({len(items)}个):")
        for i, item in enumerate(items, 1):
            print(f"  {i}. {item.get('Title', '未知商品')}")
            print(f"     ASIN: {item.get('ASIN')}")
            print(f"     SKU: {item.get('SellerSKU')}")
            print(f"     数量: {item.get('QuantityOrdered')}")
            print(f"     价格: {item.get('ItemPrice', {}).get('Amount')} {item.get('ItemPrice', {}).get('CurrencyCode')}")
            print()
            
        return order
        
    except Exception as e:
        logger.error(f"获取订单 {order_id} 失败: {str(e)}")
        print(f"错误: {str(e)}")
        return None
def get_client():
    client_id = "amzn1.application-oa2-client.948e72cc2a2d45e9a54e3ca191284bfb"
    client_secret = "amzn1.oa2-cs.v1.dd8279b7214a5d7a98b834b585f6ac1d892d2e8bbcf33d7b235757af41c1249c"
    refresh_token = "Atzr|IwEBINWJhPgyeG9bfXsHeduz71-c6ykUzBXov-urbj8o5wIJYz3iIogkYLnTSbV-Rndn_bkV0AtORZcvsqKQFR-c-6KRBZSj9A35siEzLJ_gH0euDb8U_rcZepee9FcjOFU4AMXz1JlVgJEYNHpu5j16e3d0D1cIZIeEw1i9UUGxXlRnE9TMFsmCypzgoYoEOKN7HUDiyOn_ODfR-iQzaDWVGfD2kIAPZCrhmgZ4zWeq3uB3b9jX1Yhokt9EBVp9L_hJkxNUZYjh2VRxdMwMuSb-d8Vjd-6lywbdGjdjzC9zR0gIiD2k7qZtdmyXIhVonq7Mu3Ulhi6yUMp_1GtNnpkn-Zur"   
    success, token = get_amazon_access_token(client_id, client_secret, refresh_token)
    credentials = {
        "access_token": "Atza|IwEBIK9w720E4v_Lude0wu0mCraPw0qSp_6kFQ8jyjUf1TVg9unCyGnWFlmJjbHW-X-jlkSOSszzVgu7SmWmG3zpkH2o0Ie-JWvknV1JCbF9pJNZhsR0vHvx36p2diVP-XRjnu6DqfH3jOEq2OItUhpSyKyer-OH_mmmzXiQWQiZJ8-1qlxBvv_NxQIZIAIEL1odromAzbxstnsmDnPfohY4Q_RIuWawuQfWInm5AC7OVBJk8qXo8Pa1Fp2RrnEEtKTdjXLBhmX7-VIrV-FMxVSPorUcbaY4YAFr0lKV1RJLIPI0-SLJJx9fssCnqbvJP1OoojZSju1TgPRWRMRSs9C3OKng",
        "refresh_token": "Atzr|IwEBINWJhPgyeG9bfXsHeduz71-c6ykUzBXov-urbj8o5wIJYz3iIogkYLnTSbV-Rndn_bkV0AtORZcvsqKQFR-c-6KRBZSj9A35siEzLJ_gH0euDb8U_rcZepee9FcjOFU4AMXz1JlVgJEYNHpu5j16e3d0D1cIZIeEw1i9UUGxXlRnE9TMFsmCypzgoYoEOKN7HUDiyOn_ODfR-iQzaDWVGfD2kIAPZCrhmgZ4zWeq3uB3b9jX1Yhokt9EBVp9L_hJkxNUZYjh2VRxdMwMuSb-d8Vjd-6lywbdGjdjzC9zR0gIiD2k7qZtdmyXIhVonq7Mu3Ulhi6yUMp_1GtNnpkn-Zur",
        "client_id": "amzn1.application-oa2-client.948e72cc2a2d45e9a54e3ca191284bfb",
        "client_secret": "amzn1.oa2-cs.v1.dd8279b7214a5d7a98b834b585f6ac1d892d2e8bbcf33d7b235757af41c1249c",
        "token_type": "bearer",
        "expires_in": 3600,
        "expires_at": "2025-05-17 20:11:09"
    }
    new_time = datetime.now() + timedelta(minutes=60)

# 转换为ISO格式字符串
    new_time_iso = new_time.isoformat()
    credentials = {
        "access_token": token,
        "refresh_token": refresh_token,
        "client_id": "amzn1.application-oa2-client.948e72cc2a2d45e9a54e3ca191284bfb",
        "client_secret": "amzn1.oa2-cs.v1.dd8279b7214a5d7a98b834b585f6ac1d892d2e8bbcf33d7b235757af41c1249c",
        "token_type": "bearer",
        "expires_in": 3600,
        "expires_at": new_time_iso
    }
    client = AmazonOrdersClient(credentials)
    return client

def main():
    """主函数"""
    print("=" * 60)
    print("亚马逊订单下载工具")
    print("=" * 60)
    
    # 从提供的信息中获取凭证
    
    client = get_client()
    while True:
        print("\n请选择操作:")
        print("1. 获取特定订单信息 (114-5246748-8149017)")
        print("2. 下载最近30天的订单")
        print("3. 下载缺乏明细的订单")
        print("4. 退出")
        
        # choice = input("请输入选项 (1-4): ").strip()
        choice = '3'
        if choice == '1':
            # 获取特定订单
            order_id = "114-5246748-8149017"  # 使用测试中提供的订单ID
            print(f"\n正在获取订单 {order_id}...")
            get_specific_order(client, order_id)
            
        elif choice == '2':
            # 下载最近30天的订单
            file_path = os.path.join(os.getcwd(), "amazon_orders_30days.json")
            if not file_path:
                file_path = "amazon_orders_30days.json"
                
            print(f"\n正在下载最近30天的订单到 {file_path}...")
            
            # count = client.download_orders_to_file(file_path, days=30)
            orders = client.get_amazon_orders(access_token=client.credentials['access_token'])
            import_amazon_orders(orders)
            print(orders)
            # print(f"已下载 {count} 个订单")
            
        elif choice == '3':
            # 下载最近90天的订单
            fetch_missing_order_items()
            
        elif choice == '4':
            print("\n感谢使用亚马逊订单下载工具！")
            break
            
        else:
            print("\n无效的选择，请重试。")
            

def fetch_missing_order_items():
    """
    查找所有没有关联订单明细的 Amazon 订单，然后获取并保存它们的订单明细
    
    Returns:
        tuple: (处理的订单数量, 获取的订单项目数量)
    """
    # 配置日志
    logging.basicConfig(
        level=logging.INFO,
        format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
    )
    logger = logging.getLogger('amazon_order_items_fetch')
    
    # 数据库连接
    engine = create_engine(DATABASE_URL)
    Session = sessionmaker(bind=engine)
    session = Session()
    
    # 初始化API客户端
     
    # 统计计数
    processed_orders = 0
    total_items_fetched = 0
    
    try:
        # 查询缺少订单明细的订单
        # 子查询：找出已有订单明细的订单ID
        client = get_client()
        orders_with_items_subquery = session.query(AmazonOrderItem.order_id).distinct().subquery()
        
        # 主查询：找出不在上述子查询中的订单
        orders_without_items = session.query(AmazonOrder).filter(
            ~AmazonOrder.id.in_(orders_with_items_subquery)
        ).all()
        
        total_orders = len(orders_without_items)
        logger.info(f"找到 {total_orders} 个没有订单明细的订单")
        items_added=0
        # 逐个处理订单
        for order in orders_without_items:
            try:
                logger.info(f"处理订单 {order.amazon_order_id} ({processed_orders + 1}/{total_orders})")
                
                # 调用API获取订单明细
                order_items = session.query(AmazonOrderItem).filter(AmazonOrderItem.order_id == order.id).all()
                if not order_items:
                    logger.warning(f"订单 {order.amazon_order_id} 没有明细项目")
                    processed_orders += 1
                     
                order_items = get_specific_order(client, order.amazon_order_id)
                order_data=order_items
                amazon_order_id = order_data.get('AmazonOrderId')
                existing_order = session.query(AmazonOrder).filter_by(amazon_order_id=amazon_order_id).first()
                amazon_order = existing_order
                if 'Items' in order_data and order_data['Items']:
                    items = order_data['Items']
                    
                    for item in items:
                        # 提取订单项ID
                        order_item_id = item.get('OrderItemId')
                        if not order_item_id:
                            print(f"订单项缺少OrderItemId，跳过")
                            continue                       
                        # 检查订单项是否已存在
                        existing_item = session.query(AmazonOrderItem).filter_by(
                            order_item_id=order_item_id
                        ).first()
                        
                        if existing_item:
                            print(f"订单项已存在: {order_item_id}")
                            continue
                        
                        # 提取价格信息
                        item_price = None
                        item_price_currency = None
                        if 'ItemPrice' in item:
                            if 'Amount' in item['ItemPrice']:
                                item_price = Decimal(str(item['ItemPrice']['Amount']))
                            item_price_currency = item['ItemPrice'].get('CurrencyCode')
                        
                        # 提取税费信息
                        item_tax = None
                        if 'ItemTax' in item and 'Amount' in item['ItemTax']:
                            item_tax = Decimal(str(item['ItemTax']['Amount']))
                        
                        # 提取运费信息
                        shipping_price = None
                        if 'ShippingPrice' in item and 'Amount' in item['ShippingPrice']:
                            shipping_price = Decimal(str(item['ShippingPrice']['Amount']))
                        
                        shipping_tax = None
                        if 'ShippingTax' in item and 'Amount' in item['ShippingTax']:
                            shipping_tax = Decimal(str(item['ShippingTax']['Amount']))
                        
                        # 提取促销折扣
                        promotion_discount = None
                        if 'PromotionDiscount' in item and 'Amount' in item['PromotionDiscount']:
                            promotion_discount = Decimal(str(item['PromotionDiscount']['Amount']))
                        
                        # 提取礼品包装费
                        gift_wrap_price = None
                        if 'GiftWrapPrice' in item and 'Amount' in item['GiftWrapPrice']:
                            gift_wrap_price = Decimal(str(item['GiftWrapPrice']['Amount']))
                        
                        # 创建新订单项
                        new_item = AmazonOrderItem(
                            order_id=amazon_order.id,
                            order_item_id=order_item_id,
                            asin=item.get('ASIN', ''),
                            seller_sku=item.get('SellerSKU'),
                            title=item.get('Title'),
                            quantity_ordered=int(item.get('QuantityOrdered', 0)),
                            quantity_shipped=int(item.get('QuantityShipped', 0)),
                            item_price=item_price,
                            item_price_currency=item_price_currency,
                            item_tax=item_tax,
                            shipping_price=shipping_price,
                            shipping_tax=shipping_tax,
                            promotion_discount=promotion_discount,
                            condition=item.get('ConditionId'),
                            condition_note=item.get('ConditionNote'),
                            is_gift=bool(item.get('IsGift')),
                            gift_message=item.get('GiftMessageText'),
                            gift_wrap_price=gift_wrap_price
                        )
                        time.sleep(10*random())
                        session.add(new_item)
                        session.commit()

                        items_added += 1
                        print(f"添加订单项: {order_item_id}")
                        
                        # 提交事务
                                
                
                items_count = len(order_data)
                logger.info(f"获取到 {order_items} 个子订单项目")
                
                # 保存订单明细 
                
                # 每处理一个订单提交一次，避免丢失数据
                
                processed_orders += 1
                
                # 添加延迟，避免API调用过于频繁
                time.sleep(1)
                
            except Exception as e:
                logger.error(f"处理订单 {order.amazon_order_id} 时出错: {str(e)}")
                traceback.print_exc()
                # 回滚当前订单的更改
                session.rollback()
                # 继续处理下一个订单
        session.commit()
        logger.info(f"处理完成! 共处理 {processed_orders} 个订单，获取 {total_items_fetched} 个订单项目")
        return (processed_orders, total_items_fetched)
    
    except Exception as e:
        logger.error(f"获取订单明细过程中出错: {str(e)}")
        traceback.print_exc()
        session.rollback()
        return (0, 0)
    
    finally:
        session.close()
def get_amazon_access_token(
    client_id,
    client_secret,
    refresh_token,
    token_file="amazon_token.json",
    force_refresh=False
):
    """
    获取Amazon SP-API访问令牌，60分钟有效期
    
    参数:
        client_id (str): LWA客户端ID
        client_secret (str): LWA客户端密钥  
        refresh_token (str): 刷新令牌
        token_file (str): 令牌保存的文件路径，默认为"amazon_token.json"
        force_refresh (bool): 是否强制刷新令牌，默认为False
        
    返回:
        tuple: (成功标志(bool), 访问令牌或错误信息(str))
    """
    # 尝试从文件加载现有令牌
    token_data = {}
    if os.path.exists(token_file) and not force_refresh:
        try:
            with open(token_file, 'r') as f:
                token_data = json.load(f)
                
            # 检查令牌是否仍然有效（提前5分钟刷新）
            if 'access_token' in token_data and 'expires_at' in token_data:
                expires_at = datetime.fromisoformat(token_data['expires_at'])
                if datetime.now() < (expires_at - timedelta(minutes=5)):
                    print(f"使用现有令牌，有效期至 {expires_at}")
                    return True, token_data['access_token']
        except Exception as e:
            print(f"读取令牌文件时出错: {e}")
            # 继续流程以获取新令牌
    
    # 需要获取新令牌
    print("获取新的访问令牌...")
    
    # 准备请求参数
    url = "https://api.amazon.com/auth/o2/token"
    headers = {"Content-Type": "application/x-www-form-urlencoded"}
    payload = {
        "grant_type": "refresh_token",
        "refresh_token": refresh_token,
        "client_id": client_id,
        "client_secret": client_secret
    }
    
    try:
        # 发送请求
        response = requests.post(url, headers=headers, data=payload)
        response.raise_for_status()  # 如果请求失败则引发异常
        
        # 解析响应
        response_data = response.json()
        
        # 检查必要字段
        if 'access_token' not in response_data:
            return False, "API响应中缺少访问令牌"
        
        # 添加过期时间（ISO格式）
        expires_in = response_data.get('expires_in', 3600)  # 默认60分钟
        expires_at = datetime.now() + timedelta(seconds=expires_in)
        response_data['expires_at'] = expires_at.isoformat()
        
        # 保存令牌到文件
        try:
            with open(token_file, 'w') as f:
                json.dump(response_data, f, indent=2)
            
            # 限制文件权限（仅在类Unix系统上有效）
            try:
                os.chmod(token_file, 0o600)
            except:
                pass
        except Exception as e:
            print(f"保存令牌到文件时出错: {e}")
            # 即使保存失败，仍然返回获取的令牌
        
        print(f"成功获取新令牌，有效期至 {expires_at}")
        return True, response_data['access_token']
        
    except requests.RequestException as e:
        error_msg = f"获取令牌失败: {str(e)}"
        
        # 尝试提取错误详情
        if hasattr(e, "response") and e.response:
            try:
                error_data = e.response.json()
                error_msg = f"获取令牌失败: {error_data.get('error_description', error_data.get('error', '未知错误'))}"
            except:
                pass
                
        print(error_msg)
        return False, error_msg
if __name__ == "__main__":
    try:
        main()
    except KeyboardInterrupt:
        print("\n\n程序已被用户中断")
    except Exception as e:
        logger.error(f"程序运行出错: {str(e)}")
        print(f"\n发生错误: {str(e)}")
        import traceback
        traceback.print_exc()